/*
 * Amkai Enterprise Solution (AES.) 
 * Copyright 2004 by Amkai(TM), Inc.
 * www.amkai.com
 * 
 * Project: webservice
 * Package: com.amkai.aes.webservice.binary.objecthandler
 * File: PersonUniqueQueryHandler.java
 * Created: Feb 23, 2006 1:07:19 PM
 * Author: johnny 
 * 
 */
package com.amkai.aes.webservice.binary.objecthandler;

import java.io.Serializable;
import java.sql.Timestamp;
import java.util.Date;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Query;

import com.amkai.aes.aescommon.service.binary.job.PersonUniqueJob;
import com.amkai.aes.aescommon.service.result.PersonUniqueResult;
import com.amkai.aes.webservice.binary.JobObjectHandler;
import com.amkai.common.extensions.hibernate.config.SiteManager;
import com.amkai.common.extensions.hibernate.config.manager.SessionManager;
import com.sun.corba.se.pept.encoding.InputObject;

/**
 * PersonUniqueQueryHandler
 * @author johnny
 * Description:
 *
 */
public class PersonUniqueQueryHandler implements JobObjectHandler<PersonUniqueJob, Serializable, PersonUniqueResult> {

	private static final Log log = LogFactory.getLog(GeneralHandler.class);

	
	/** 
	 * Overriden method
	 * @param job
	 * @param object
	 * @return
	 * @throws Exception
	 * @see com.amkai.aes.webservice.binary.JobObjectHandler#process(E, InputObject)
	 * 
	 */
	public PersonUniqueResult process(PersonUniqueJob job, Serializable object) throws Exception {
		PersonUniqueResult result = new PersonUniqueResult();
		
		SessionManager sm = SiteManager.Start();
		
		try {
			
			sm.openSession();
			
			String lastName = job.getLastName();
			String firstName = job.getFirstName();
			String maidenName= job.getMaidenName();
			Date dateOfBirth = job.getDataOfBirth();
			String ssn = job.getSsn();
			String phoneNumber = job.getPhoneNumber();
			Long personId = job.getPersonid();
			Boolean isMale = job.getIsMale();
			
			
			if (lastName.length() > 2)
				lastName = lastName.substring(0, 3).toUpperCase() + "%"; 
			else
				lastName = lastName.toUpperCase() + "%";
			
			firstName = firstName.substring(0,1).toUpperCase() + "%";
			
			String querys = "SELECT p.id, p.lastName, p.firstName, p.maidenName, p.dateOfBirth, p.ssn, ph.number, p.isMale \n" +
								 "FROM Person p \n" +
								 "LEFT JOIN p.homeAddress h \n" +
								 "LEFT JOIN h.phone ph \n" +
								 "WHERE ";
			
			if (personId != null) {
				querys += " p.id != :personid AND (";
			}
			else {
				querys += "(";
			}
			
			querys += "(upper(p.firstName) like :firstname AND upper(p.lastName) like :lastname ) \n";
			
			if (ssn != null) {
				querys += " OR (p.ssn = :ssn) \n";
			}
			
			if (dateOfBirth != null && isMale != null) {
				querys += " OR (p.dateOfBirth = :dob AND p.isMale = :ismale) \n";
			}
			
			String maidenFirst = null;
			String maidenLast = null;
			
			if (maidenName != null) {
				String[] maidenNameParts = maidenName.split(" ");
				
				if (maidenNameParts != null && maidenNameParts.length > 1) {
					
					if (maidenNameParts[0].contains(",")) {
						maidenLast = maidenNameParts[0].substring(0, maidenNameParts[0].indexOf(",")).toUpperCase() + "%";
						maidenFirst = maidenNameParts[maidenNameParts.length - 1].toUpperCase() + "%";
					}
					else {
						maidenFirst = maidenNameParts[0].toUpperCase() + "%";
						maidenLast = maidenNameParts[maidenNameParts.length - 1].toUpperCase() + "%";
					}
					
					if (maidenLast.length() > 2)
						maidenLast = maidenLast.substring(0, 3).toUpperCase() + "%"; 
					else
						maidenLast = maidenLast.toUpperCase() + "%";
					
					maidenFirst = maidenFirst.substring(0,1).toUpperCase() + "%";

					querys += " OR (upper(p.firstName) like :maidenfirst AND upper(p.lastName) like :maidenlast AND (p.isMale IS NULL OR p.isMale = false) ) \n";
				}
			}
			
			querys += ")";
			
			Query query = sm.execute(querys);
			
			if (personId != null) {
				query = query.setLong("personid", personId);
			}
			
			query = query.setString("lastname", lastName);
			query = query.setString("firstname", firstName);

			if (dateOfBirth != null && isMale != null) {
				query = query.setDate("dob", new Timestamp(dateOfBirth.getTime()));
				if (isMale.booleanValue())
					query = query.setInteger("ismale", 1);
				else
					query = query.setInteger("ismale", 0);
			}
			
			if (ssn != null) {
				query = query.setString("ssn", ssn);
			}
			
			if (maidenLast != null && maidenFirst != null) {
				query = query.setString("maidenlast", maidenLast);
				query = query.setString("maidenfirst", maidenFirst);
			}
			

			
			List resultList = query.list();
			
			Object[][] r = new Object[resultList.size()][];
			for (int i = 0; i< resultList.size(); i++ ) {
				r[i] = (Object[])resultList.get(i);
			}
			
			result.setDatas(r);
			
		} catch (Exception e) {
			log.error("",e);
			throw e;
		}
		finally {
			sm.closeSession();
		}
		return result;
	}

}
